package com.lakers.notice.springboot.db;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.PageUtil;
import cn.hutool.core.util.StrUtil;
import com.lakers.notice.springboot.model.NoticeMessageQueryInfo;
import com.lakers.notice.springboot.model.NoticeReadStatus;
import com.lakers.notice.springboot.util.SqlInUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 功能描述: StorageHelp
 *
 * @author liqin(zxl)
 * @date 2024/5/3
 */
@Component
@Slf4j
public class StorageHelp {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public Long save(MessageDo message){
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(con -> {
            final PreparedStatement ps = con.prepareStatement(SqlStatement.INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,message.getSendId());
            ps.setString(2,message.getRecvIds());
            ps.setString(3, message.getContent());
            ps.setString(4, message.getBusinessType());
            ps.setString(5, message.getBusinessId());
            ps.setInt(6,message.getType());
            return ps;
        }, holder);
        return Objects.requireNonNull(holder.getKey()).longValue();
    }

    public void trySave(List<MessageUserDo> messageUsers){
        final List<Long> noticeIds = jdbcTemplate.queryForList(String.format(SqlStatement.SELECT_NOTICE_USER_SQL, SqlInUtils.in(messageUsers.stream().map(MessageUserDo::getNoticeId).collect(Collectors.toList())), messageUsers.get(0).getUserId()), Long.class);
        final List<MessageUserDo> saves = messageUsers.stream().filter(e -> !noticeIds.contains(e.getNoticeId())).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(saves)){
            return;
        }
        jdbcTemplate.batchUpdate(SqlStatement.INSERT_USER_SQL, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setLong(1,saves.get(i).getNoticeId());
                ps.setString(2, saves.get(i).getUserId());
            }
            @Override
            public int getBatchSize() {
                return saves.size();
            }
        });
    }


    public Integer messageCount(NoticeMessageQueryInfo messageQuery){
        String sql = SqlStatement.SELECT_MESSAGE_COUNT_SQL + where(messageQuery);
        final Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        if (Objects.isNull(count)){
            return 0;
        }
        return count;
    }

    public List<MessageDo> messageList(NoticeMessageQueryInfo messageQuery){
        String sql = SqlStatement.SELECT_MESSAGE_LIST_SQL +  where(messageQuery) + limitOrder(messageQuery.getPage(), messageQuery.getPageSize());
        final List<MessageDo> messages = jdbcTemplate.query(sql, (rs, rowNum) -> {
            MessageDo messageDo = new MessageDo();
            messageDo.setSendId(rs.getString(1));
            messageDo.setContent(rs.getString(2));
            messageDo.setBusinessType(rs.getString(3));
            messageDo.setBusinessId(rs.getString(4));
            messageDo.setSendTime(DateUtil.format(rs.getTimestamp(5).toLocalDateTime(), DatePattern.NORM_DATETIME_PATTERN));
            messageDo.setMessageId(rs.getLong(6));
            messageDo.setType(rs.getInt(7));
            messageDo.setStatus(Objects.isNull(messageQuery.getStatus()) ? NoticeReadStatus.un_read.getCode() : messageQuery.getStatus());
            return messageDo;
        });
        if (Objects.nonNull(messageQuery.getStatus())){
            return messages;
        }
        final Set<Long> messageIds = messages.stream().map(MessageDo::getMessageId).collect(Collectors.toSet());
        final List<Long> noticeIds = jdbcTemplate.queryForList(String.format(SqlStatement.SELECT_NOTICE_USER_SQL, SqlInUtils.in(new ArrayList<>(messageIds)), messageQuery.getUserId()), Long.class);
        for (MessageDo message : messages) {
            if (noticeIds.contains(message.getMessageId())){
                message.setStatus(NoticeReadStatus.read.getCode());
            }
        }
        return messages;
    }


    private String where(NoticeMessageQueryInfo messageQuery){
        StringBuilder whereBuilder = new StringBuilder();
        whereBuilder.append(String.format(" and %s", findInset(Arrays.stream(messageQuery.getUserId().split(StrUtil.COMMA)).collect(Collectors.toList()))));

        if (StrUtil.isNotEmpty(messageQuery.getBusinessId())){
            whereBuilder.append(String.format(" and find_in_set(t.business_id, '%s')", messageQuery.getBusinessId()));
        }
        if (StrUtil.isNotEmpty(messageQuery.getBusinessType())){
            whereBuilder.append(String.format(" and find_in_set(t.business_type, '%s')", messageQuery.getBusinessType()));
        }
        if (StrUtil.isNotEmpty(messageQuery.getStartTime()) && StrUtil.isNotEmpty(messageQuery.getEndTime())){
            whereBuilder.append(String.format(" and t.send_time between %s to %s", messageQuery.getStartTime(), messageQuery.getEndTime()));
        }
        if (NoticeReadStatus.read.getCode().equals(messageQuery.getStatus())){
            whereBuilder.append(String.format(" and t.id in (select distinct(notice_id) from tb_notice_user where find_in_set(user_id, '%s') )",messageQuery.getUserId()));
        }
        if (NoticeReadStatus.un_read.getCode().equals(messageQuery.getStatus())){
            whereBuilder.append(String.format(" and t.id not in (select distinct(notice_id) from tb_notice_user where find_in_set(user_id, '%s') )", messageQuery.getUserId()));
        }
        if (Objects.nonNull(messageQuery.getType())){
            whereBuilder.append(String.format(" and t.type = '%s'", messageQuery.getType()));
        }
        return whereBuilder.toString();
    }

    private String limitOrder(Integer page, Integer pageSize){
        final int start = PageUtil.getStart(page -1, pageSize);
        return " order by t.send_time desc limit " + start + "," + pageSize + "";
    }

    private String findInset(List<String> userIds){
        StringBuilder sb = new StringBuilder(" (");
        int i = 0;
        for (String userId : userIds) {
            sb.append(String.format("find_in_set('%s',recv_ids)", userId));
            if(i < userIds.size() - 1){
                sb.append(" or ");
            }
            i++ ;
        }
        sb.append(" or recv_ids = 'system_to_all'");
        sb.append(")");
        return sb.toString();
    }


}
